USE AdventureWorks
GO

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO

-- DROP TABLE AuctionItem

-- Create Table, using the new UDT

CREATE TABLE AuctionItem (
  ID    int,
  Item  nvarchar(50),
  Price Currency)
GO

-- Add some items

INSERT AuctionItem VALUES (1,'Toaster','50 USD')
INSERT AuctionItem VALUES (2,'Microwave Oven','30 EUR')
INSERT AuctionItem VALUES (3,'Blender','65 ILS')

-- Test

SELECT Item,
       Price.Amount as Amount,
       Price.CurrencyCode as CurrencyCode
  FROM AuctionItem

-- Conversion Web Service: http://localhost/currencyws/Service.asmx

-- Test simple Aggregate Function

SELECT dbo.CharacterCount(Item) FROM AuctionItem

SELECT Item, dbo.CharacterCount(Item) as Characters
  FROM AuctionItem GROUP BY Item

-- How do find the most expensive item?

SELECT MAX(Price) FROM AuctionItem

-- Add our Serialization Assembly

-- DROP ASSEMBLY [CoolDbObjectsXML]

CREATE ASSEMBLY [CoolDbObjectsXML]
  FROM 'C:\Demo\Seminar\CoolDBObjects\bin\Debug\CoolDBObjects.XmlSerializers.dll'
  WITH PERMISSION_SET = SAFE
GO

-- Try our new function

SELECT dbo.MAXCURRENCY(Price) FROM AuctionItem

-- Add a more expensive item

INSERT AuctionItem VALUES (4,'Espresso Maker','55 EUR')
SELECT dbo.MAXCURRENCY(Price) FROM AuctionItem

-- Done

DROP TABLE AuctionItem

DROP ASSEMBLY [CoolDbObjectsXML]